package com.cn.ccp.service.system;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;

import com.cn.ccp.bean.RoleBean;
import com.cn.ccp.service.BasicService;
import com.cn.ccp.util.CCPContants;


public class RoleService extends BasicService {


	public boolean sava(final RoleBean roleBean ,final String selectRoleMenu) {
		final int index = CCPContants.getClobalIndex();
		transactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus arg0) {
				try {
					final String sql = "insert into role (rolename,descript) values (?,?)";
					KeyHolder keyHolder = new GeneratedKeyHolder();  
					jdbcTemplate.update(  new PreparedStatementCreator() {  
							    public PreparedStatement createPreparedStatement(Connection con) throws SQLException   
							    {  
								     	PreparedStatement ps =con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
								     	ps.setString(1, roleBean.getRolename());
										ps.setString(2, roleBean.getDescript());
										
								     return ps;  
							    }  
							   }, keyHolder);  
					final int roleid = keyHolder.getKey().intValue();
					
					
					String sql2 = "delete from role_nemu where role_id = ?";
					jdbcTemplate.update(sql2 ,new Object[]{roleid});
					
					sql2 = "insert into role_nemu(role_id ,menu_id) values (?,?)";
					
					String[] strs = selectRoleMenu.split(",");
					for(String s : strs){
						s = s.trim();
						if(!s.equals(""))
							jdbcTemplate.update(sql2 , new Object[]{roleid ,s});
					}
					CCPContants.tranmitResultMap.put(index, true);
				} catch (Exception e) {
					CCPContants.tranmitResultMap.put(index, false);
				}
				return null;
			}
		});
		return (Boolean) CCPContants.tranmitResultMap.get(index) ;
	}

	public boolean update(final RoleBean bean , final String selectRoleMenu) {
		final int index = CCPContants.getClobalIndex();
		transactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus arg0) {
				try {
					String sql = "update role set rolename = ? , descript = ? where id = ?";
					jdbcTemplate.update(sql ,new Object[]{bean.getRolename(),bean.getDescript(),bean.getId()});
					
					String sql2 = "delete from role_nemu where role_id = ?";
					jdbcTemplate.update(sql2 ,new Object[]{bean.getId()});
					
					sql2 = "insert into role_nemu(role_id ,menu_id) values (?,?)";
					
					String[] strs = selectRoleMenu.split(",");
					for(String s : strs){
						s = s.trim();
						if(!s.equals(""))
							jdbcTemplate.update(sql2 , new Object[]{bean.getId() ,s});
					}
					CCPContants.tranmitResultMap.put(index, true);
				} catch (Exception e) {
					CCPContants.tranmitResultMap.put(index, false);
				}
				return null;
			}
		});
		return (Boolean) CCPContants.tranmitResultMap.get(index) ;
	}

	public boolean delete(final int id) {
		
		final int index = CCPContants.getClobalIndex();
		transactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus arg0) {
				try {
					String sql2 = "delete from role_nemu where role_id = ?";
					jdbcTemplate.update(sql2 ,new Object[]{id});
					String sql = "delete from role where id = ?";
					jdbcTemplate.update(sql ,new Object[]{id});
					
					CCPContants.tranmitResultMap.put(index, true);
				} catch (Exception e) {
					CCPContants.tranmitResultMap.put(index, false);
				}
				return null;
			}
		});
		return (Boolean) CCPContants.tranmitResultMap.get(index) ;
	}


	public Object findById(int id) {
		String sql = "SELECT * FROM role WHERE id = ?";
		return jdbcTemplate.queryForObject(sql, new Object[] { id }, new RolesMapper());
	}
	
	
	public boolean roleNameIsAlready(int id ,String roleName) {
		String sql = "";
		Object[] objs = null ;
		if(id == 0 ){
			sql = "SELECT * FROM role WHERE rolename = ? ";
			objs = new Object[] { roleName };
		}else{
			sql = "SELECT * FROM role WHERE rolename = ? and id != ?";
			objs = new Object[] { roleName,id };
		}
		return jdbcTemplate.query(sql, objs, new RolesMapper()).size() > 0;
	}

	public List pageQuery(int start, int pagesize) {
		String sql = "select * from role limit ?,?";
		return jdbcTemplate.query(sql, new Object[] { start,pagesize},new RolesMapper());
	}
	
	public List queryAll() {
		String sql = "select * from role";
		return jdbcTemplate.query(sql,new RolesMapper());
	}

	public int getCount() {
		String sql = "select count(1) from role ";
		return jdbcTemplate.queryForInt(sql);
	}
	
	public List<RoleBean> getUserRoleList(int userid){
		String sql = "SELECT r.* FROM role_user  ru LEFT JOIN role r ON r.id = ru.role_id WHERE ru.user_id = ?";
		return jdbcTemplate.query(sql,new Object[]{userid}, new RolesMapper());
	}
	public List<RoleBean> getUserNoRoleList(int userid){
		String sql = "SELECT * FROM role r WHERE r.id NOT IN (SELECT ru.role_id FROM role_user ru WHERE ru.user_id = ?)";
		return jdbcTemplate.query(sql,new Object[]{userid}, new RolesMapper());
	}
	
	
	public class RolesMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			RoleBean rolesBean = new RoleBean();
			rolesBean.setId(rs.getInt("id"));
			rolesBean.setRolename(rs.getString("rolename"));
			rolesBean.setDescript(rs.getString("descript"));
			return rolesBean;
		}
	}
	
	
	public class RoleProportion{
		private String roleName ;
		private int userCount ;
		public String getRoleName() {
			return roleName;
		}
		public void setRoleName(String roleName) {
			this.roleName = roleName;
		}
		public int getUserCount() {
			return userCount;
		}
		public void setUserCount(int userCount) {
			this.userCount = userCount;
		}
	}
	
	public List<RoleProportion> getRoleProportionList(){
		String sql = "SELECT r.rolename ,COUNT(ru.user_id) as userCount FROM role_user  ru "+
						" LEFT JOIN role r ON r.id = ru.role_id "+
						" GROUP BY ru.role_id";
		return jdbcTemplate.query(sql, new RoleProportionMapper());
	}
	
	public class RoleProportionMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			RoleProportion bean = new RoleProportion();
			bean.setRoleName(rs.getString("rolename"));
			bean.setUserCount(rs.getInt("userCount"));
			return bean;
		}
	}
}
